﻿CREATE PROCEDURE [dbo].[usp_insert_AuditLog]
(
      @ActionName      varchar(50)
	 ,@MessageID       uniqueidentifier
	 ,@MessageData     varchar(max)		= NULL
	 ,@ResubmitUrl	   varchar(512)		= NULL
	 ,@ResubmitCode    varchar(3)		= NULL
	 ,@ResubmitMessage varchar(50)		= NULL
	 ,@AuditUserName   varchar(256)
	 ,@Debug           bit = 0
)
AS

/****************************************************************************
Name: usp_insert_AuditLog.sql
Description:  Inserts an audit record into the AuditLog table.
*****************************************************************************/
SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10);
DECLARE @ErrorCode         int;

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: ';


-- Test harness

-- declare the stored procedure paramters
/*
DECLARE @ActionName varchar(50)
DECLARE @MessageID varchar(256)
DECLARE @MessageData varchar(max)
DECLARE @AuditUserName varchar(256)
DECLARE @Debug	bit


-- now set them
SELECT
	 @ActionName      = 'Save'
	 ,@MessageID      = 'b96a53f6-7fdc-40f4-9577-14e6d14fe0eb'
	 ,@MessageData    = '<Body><Foo>Bar</Foo></Body>'
	 ,@AuditUserName  = 'domainname\username'
     ,@Debug                 = 1
*/


-- Print parameters
/*IF (@Debug > 0)
BEGIN
	PRINT @DebugPrefix + '@ActionName = ' + @ActionName 
	PRINT @DebugPrefix + '@MessageID = ' + CONVERT(varchar, @MessageID)
	PRINT @DebugPrefix + '@MessageData = ' + @MessageData
	PRINT @DebugPrefix + '@AuditUserName = ' + @AuditUserName
END;*/

--Look up the audit settings from the configuration table
DECLARE @AuditSave bit
DECLARE @AuditSuccessfulResubmit bit
DECLARE @AuditUnsuccessfulResubmit bit

SELECT @AuditSave = CAST([Value] AS bit) FROM dbo.Configuration WHERE [Name] = 'AuditSave'
SELECT @AuditSuccessfulResubmit = CAST([Value] AS bit) FROM dbo.Configuration WHERE [Name] = 'AuditSuccessfulResubmit'
SELECT @AuditUnsuccessfulResubmit = CAST([Value] AS bit) FROM dbo.Configuration WHERE [Name] = 'AuditUnsuccessfulResubmit'

IF (
	(@ActionName = 'Save' AND @AuditSave = 1)
	OR (@ActionName = 'SuccessfulResubmit' AND @AuditSuccessfulResubmit = 1)
	OR (@ActionName = 'UnsuccessfulResubmit' AND @AuditUnsuccessfulResubmit = 1)
	OR (@ActionName = 'Export' AND @AuditSave = 1)
	OR (@ActionName = 'Import' AND @AuditSave = 1)
	OR (@ActionName = 'Delete' AND @AuditSave = 1)
	OR (@ActionName = 'Create' AND @AuditSave = 1)
	)
BEGIN

	DECLARE @AuditLogID		 uniqueidentifier
	DECLARE @NativeMessageID varchar(38)
	DECLARE	@ContentType	 varchar(50)
	DECLARE @MessageName	 varchar(50)
	DECLARE @FaultID		 uniqueidentifier
	DECLARE @NativeFaultID	 varchar(38)
	DECLARE @Application	 varchar(256)

	SELECT @AuditLogID = NEWID()

	--Look up extra data from the Message table
	SELECT 
			 @NativeMessageID	= NativeMessageID
			,@ContentType		= ContentType
			,@MessageName		= MessageName
			,@FaultID			= FaultID

	FROM
			dbo.Message

	WHERE
			MessageID = @MessageID

	--Look up extra data from the Fault table
	SELECT
			@NativeFaultID = NativeMessageID
			,@Application = [Application]

	FROM
			dbo.Fault

	WHERE
			FaultID = @FaultID

	--Insert the data into the AuditLog table
	INSERT INTO
				dbo.AuditLog 
				(
					AuditLogID,
					ActionType,
					MessageID,
					NativeMessageID,
					ContentType,
					MessageName,
					FaultID,
					NativeFaultID,
					ResubmitUrl,
					ResubmitCode,
					ResubmitMessage,
					AuditUserName,
					[Application]
				)
	VALUES
				(
					@AuditLogID,
					@ActionName,
					CONVERT(uniqueidentifier, @MessageID),
					@NativeMessageID,
					@ContentType,
					@MessageName,
					@FaultID,
					@NativeFaultID,
					@ResubmitUrl,
					@ResubmitCode,
					@ResubmitMessage,
					@AuditUserName,
					@Application
				)
 
	IF @ActionName = 'SuccessfulResubmit'
	BEGIN
		--Insert the associated message data
		INSERT INTO
					dbo.AuditLogMessageData
					(
						AuditLogID,
						MessageData
					)

		VALUES
					(
						@AuditLogID,
						@MessageDAta
					)
	END
END

-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;
